package com.aixin.csopequity.repository;


import com.aixin.csopequity.domain.ServiceLevelDef;
import com.aixin.csopequity.domain.vo.SupplierServiceVo;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Optional;

@Repository
public interface ServiceLevelDefRepository extends JpaRepository<ServiceLevelDef,Long> {

    @Query(value = "select count(*) from customerownedservices where 1 = 1 " +
            "AND equityID = :equityID " +
            "AND (equityLevelID is null or equityLevelID = :equityLevelID )", nativeQuery = true)
    int queryServiceLevelDef(@Param("equityID")String equityID,@Param("equityLevelID") String equityLevelID);

    Integer deleteAllByEquityID(String equityID);

    @Query(value = "select new com.aixin.csopequity.domain.vo.SupplierServiceVo(sd.supplierID, sld.equityLevelID) from ServiceLevelDef sld join ServiceDef sd on sd.serviceID=sld.serviceID " +
            "where sld.equityLevelID in :levelID")
    List<SupplierServiceVo> getServiceDefByEquityLevelIDList(@Param("levelID") List<String> levelID);

    List<ServiceLevelDef> findAllByServiceID(String serviceID);

    Optional<ServiceLevelDef> findByServiceID(String serviceID);

    @Query(value = "select distinct sld from ServiceLevelDef sld join ServiceDef sd " +
            "on sld.serviceID=sd.serviceID where 1=1 " +
            "and (:supplierID is null or sd.supplierID=:supplierID)" +
            "and (:serviceID is null or sd.serviceID=:serviceID) ")
    List<ServiceLevelDef> findEquities(@Param("supplierID") String supplierID, @Param("serviceID") String serviceID);
}
